package main.java.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;


import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

/**
 * @Auther:Liu
 * @Testname:Myjdbc
 * @Date:2025/3/12 17:15
 */
public class Myjdbc {

    private Connection conn;

    public Myjdbc() {
        this.conn();
    }

    public static void main(String[] args) {
        Myjdbc myjdbc = new Myjdbc();
        int insert = myjdbc.insert(new Dept(50, "BBBBB", "GUANGXI"));
        System.out.println(insert);
        int delete = myjdbc.delete(50);
        System.out.println(delete);
        int update = myjdbc.update(new Dept(40, "EEEEEE", "DSSVD"));
    }

    //链接
    public void conn() {
        try {
            Properties properties = new Properties();
            FileInputStream fileInputStream = new FileInputStream("src/main/java/main/java/jdbc/Druidjdbc.properties");
            properties.load(fileInputStream);
            //德鲁伊连接池
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            this.conn = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //查找
    public ArrayList<Dept> findall() {
        String sql = "select * from dept";
        ArrayList<Dept> depts = null;
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            depts = new ArrayList<>();
            while (resultSet.next()) {
                Dept dept = new Dept();
                dept.setDeptno(resultSet.getInt("DEPTNO"));
                dept.setDname(resultSet.getString("DNAME"));
                dept.setLoc(resultSet.getString("LOC"));
                depts.add(dept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return depts;
    }

    //插入
    public int insert(Dept dept) {
        String sql = "insert dept values(?,?,?)";
        int i = 0;
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, dept.getDeptno());
            statement.setString(2, dept.getDname());
            statement.setString(3, dept.getLoc());
            i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //删除
    @Test
    public int delete(int DEPTNO) {
        String sql = "delete from dept where DEPTNO=?";
        int i = 0;
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, DEPTNO);
            i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //更新
    public int update(Dept dept) {
        String sql = "update dept set DNAME=?,LOC=? WHERE DEPTNO=?";
        int i = 0;
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, dept.getDname());
            statement.setString(2, dept.getLoc());
            statement.setInt(3, dept.getDeptno());
            i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
}
